package com.ericsson.middleware.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.ericsson.middleware.entities.ParkingSpotState;


@Component
public class ParkingSpotDao {
	private static DataSource dataSource;
 
	private final static Logger LOGGER = Logger.getLogger(ParkingSpotDao.class
			.getName());

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
 
	public void insert(ParkingSpotState parkingSpot){
 
		String sql = "INSERT INTO parking_spot_state " +
				"(ID, STATUS, LATITUDE, LONGITUDE) VALUES (?, ?, ?, ?)";
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, parkingSpot.getId());
			ps.setString(2, parkingSpot.getStatus());
			ps.setString(3, parkingSpot.getLat());
			ps.setString(4, parkingSpot.getLng());
			ps.executeUpdate();
			ps.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
 
	public void update(ParkingSpotState parkingSpot){
		 
		String sql = "UPDATE parking_spot_state " +
				" SET STATUS=?, LATITUDE=?, LONGITUDE=? " +
				" WHERE ID=?";
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, parkingSpot.getStatus());
			ps.setString(2, parkingSpot.getLat());
			ps.setString(3, parkingSpot.getLng());
			ps.setString(4, parkingSpot.getId());			
			ps.executeUpdate();
			ps.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public ParkingSpotState findById(String id){
 
		String sql = "SELECT * FROM parking_spot_state WHERE ID = ?";
 
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ParkingSpotState parkingSpot = null;
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				parkingSpot = new ParkingSpotState(
					rs.getString("ID"),
					rs.getString("STATUS"),
					rs.getString("LATITUDE"),
					rs.getString("LONGITUDE")
				);
			}
			rs.close();
			ps.close();
			return parkingSpot;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
				conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public void cleanTable(){
		 
		String sql = "DELETE FROM parking_spot_state;";
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
 
}